﻿using System.Data;
using ContractManagementSystem.EntityObjects;

namespace ContractManagementSystem.DataAccessLayer
{
    /// <summary>
    /// Create by: ChinhBV
    /// </summary>
    public static class ClientDao
    {
        #region"Get Client"
        // Get all client in tblCLients
        public static DataTable SelectAllClient()
        {
            var dataTable=CommonDao.GetDataTable("sp_SelectAllClient");
            return dataTable;
        }

        // Get a client by id
        public static DataTable SelectClientByClientId(string clientId)
        {
            var query = "sp_Client_SelectClientByClientId @ClientId='"+clientId+"'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        //view a client by user name
        public static DataTable SelectClientByUserName(string user)
        {
            var query = "sp_Client_SelectClientByUserName @UserName='"+user+"'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        #endregion

        #region"Search"
        /// <summary>
        /// Search client
        /// </summary>
        /// <param name="fullName"></param>
        /// <param name="companyName"></param>
        /// <returns></returns>
        public static DataTable SearchClient(string fullName, string companyName)
        {
            var query = "sp_Client_GetClientByFullNameAndCompanyName @FullName='" + fullName + "',"
                        + "@CompanyName='" + companyName + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        #endregion  

        #region"Insert/Update/Delete"
        /// <summary>
        /// Insert a new client
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public static string InsertClient(ClientEntity client)
        {
            var query = "sp_InsertClient "
                        + "@UserName='" + client.UserName + "',"
                        + "@FullName=N'" + client.FullName + "',"
                        + "@CompanyName=N'" + client.CompanyName + "',"
                        + "@Address=N'" + client.Address + "',"
                        + "@PhoneNumber='" + client.PhoneNumber + "',"
                        + "@Email='" + client.Email + "'";
            return CommonDao.ExecuteSqlCommand(query);
        }
        /// <summary>
        /// Update a client
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public static string UpdateClient(ClientEntity client)
        {
            var query = "sp_UpdateClient @ClientId='" + client.ClientId + "',"
                        + "@FullName=N'" + client.FullName + "',"
                        + "@CompanyName=N'" + client.CompanyName + "',"
                        + "@Address=N'" + client.Address + "',"
                        + "@PhoneNumber='" + client.PhoneNumber + "',"
                        + "@Email='" + client.Email + "'";
            return CommonDao.ExecuteSqlCommand(query);
        }
        /// <summary>
        /// Delete client
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public static string DeleteClient(ClientEntity client)
        {
            var query = "sp_DeleteClient @ClientId='" + client.ClientId + "'";
            return CommonDao.ExecuteSqlCommand(query);
        }
        #endregion

        public static bool CheckExistEmailValue(string value)
        {
            string sql = "SELECT Email FROM tblClients WHERE Email = '" + value + "'";
            DataTable dataTable = CommonDao.GetDataTable(sql);
            if (dataTable.Rows.Count > 0)
                return true;
            return false;
        }
    }
}